SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO


CREATE PROCEDURE dbo.[DnnProduct_AutomaticStuckOrderHandling_DBServerList]
	
AS
BEGIN
	DECLARE c CURSOR READ_ONLY
	FOR SELECT [Name] FROM sys.databases
		WHERE [Name] NOT LIKE '%cap%'
		and [Name] NOT LIKE '%demo%'
		and [Name] NOT LIKE '%dnn%'
		and [Name] NOT LIKE '%dev%'
		and [Name] NOT LIKE '%staging%'
		and [Name] NOT LIKE '%sync%'
		and [Name] NOT LIKE '%temp%'
		and [Name] NOT LIKE '%test%'
		and [Name] NOT LIKE '%backup%'
		and [Name] NOT LIKE '%upgrade%'
		and [Name] NOT LIKE '%acs%'
        and [Name] <> 'model'
		and [Name] <> 'PWRewrite'
		and [Name] <> 'adept'

	CREATE TABLE #table (DatabaseName VARCHAR(255), PWVersion VARCHAR(50))

	DECLARE @db NVARCHAR(255)
	DECLARE @sql NVARCHAR(MAX)

	OPEN c
	FETCH NEXT FROM c INTO @db
	WHILE (@@FETCH_STATUS <> -1)
	BEGIN
		
		DECLARE @IsPW10 INT
		DECLARE @IsPWC INT
		
		SET @sql = 'SELECT @IsPWCR = COUNT(*) FROM ' + @db + '.INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = ''ServerSettings'''
		EXECUTE sp_executesql @sql, N'@IsPWCR int OUTPUT', @IsPWCR = @IsPWC OUTPUT
		
		IF (@IsPWC > 0)
			BEGIN
				INSERT INTO #table (DatabaseName, PWVersion)
				VALUES (@db, 'PWC')
			END
		ELSE
			BEGIN
				SET @sql = 'SELECT @IsPW10R = COUNT(*) FROM ' + @db + '.INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = ''cw_Settings_Server'''
				EXECUTE sp_executesql @sql, N'@IsPW10R int OUTPUT', @IsPW10R = @IsPW10 OUTPUT
				IF (@IsPW10 > 0)
					BEGIN
						INSERT INTO #table (DatabaseName, PWVersion)
						VALUES (@db, 'PW10')
					END
			END

		FETCH NEXT FROM c INTO @db
	END

	CLOSE c
	DEALLOCATE c

	SET @sql = N'WITH ItemRN AS (SELECT DatabaseName AS DBName, PWVersion FROM #table'

	SET @sql = @sql + N' ) SELECT DBName, PWVersion FROM ItemRN ORDER BY DBName;'
	EXECUTE sp_executesql @sql

	DROP TABLE #table
END
GO
